package hello;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

public class Database_Connection {
  private Connection connect = null;
  private Statement statement = null;
  private PreparedStatement preparedStatement = null;
  private ResultSet resultSet = null;

  public void insertIntoDatabase(String username,String title,String location,String filename,String category, String description) throws Exception {
    try {
      // this will load the MySQL driver, each DB has its own driver
      Class.forName("com.mysql.jdbc.Driver");
      // setup the connection with the DB.
      connect = DriverManager
          .getConnection("jdbc:mysql://localhost/lwif?"
              + "user=root&password=root");

      preparedStatement = connect
          .prepareStatement("insert into user_posts values (?, ?, ?, ?, ?,?)");
      preparedStatement.setString(1, username);
      preparedStatement.setString(2, title);
      preparedStatement.setString(3, description);
      preparedStatement.setString(4, location);      
      preparedStatement.setString(5, filename);
      preparedStatement.setString(6, category);
      
      preparedStatement.executeUpdate();

    } catch (Exception e) {
      throw e;
    } finally {
      close();
    }

  }
  
  //Generalized function to insert into a table given its name
  public int insert_into_table(String tablename, LinkedHashMap<String,String> row_values) throws Exception {
    int uid=0;
    try {
      // this will load the MySQL driver, each DB has its own driver
      Class.forName("com.mysql.jdbc.Driver");
      // setup the connection with the DB.
      connect = DriverManager
          .getConnection("jdbc:mysql://localhost/lwif?"
              + "user=root&password=root");
      
      //Getting the column names out
    String columnSet = "";  
    String variables = "";
    for (String entry : row_values.keySet()) {
        columnSet += entry + ",";
        variables += "?,";
    } 
    columnSet = columnSet.substring(0, columnSet.lastIndexOf(","));
    variables = variables.substring(0, variables.lastIndexOf(","));
    
      //Preparing the insert statement
      String insert_statement = "insert into " + tablename + "(" + columnSet + ") values(" + variables + ")";
      int i=1;
      preparedStatement = connect.prepareStatement(insert_statement);
      for (String entry : row_values.values()) {
        preparedStatement.setString(i, entry);
        i++;
      }
      preparedStatement.executeUpdate();
      
      String sql = "SELECT uid FROM " + "post_text_info" + " where username=?";
      preparedStatement = connect.prepareStatement(sql);
      preparedStatement.setString(1, "user");
      ResultSet rs = preparedStatement.executeQuery();
      
      while(rs.next()){
         uid  = rs.getInt("uid");
      }
      

    } catch (Exception e) {
      throw e;
    } finally {
      close();
    }
    return uid;
  }
  
  public void signup(String name,String email,String username,String password,String birthday, String sex) 
              throws Exception {
    try {
      // this will load the MySQL driver, each DB has its own driver
      Class.forName("com.mysql.jdbc.Driver");
      // setup the connection with the DB.
      connect = DriverManager
          .getConnection("jdbc:mysql://localhost/lwif?"
              + "user=root&password=root");

      preparedStatement = connect
          .prepareStatement("insert into users(name,email,username,password,birthday,sex) values (?, ?, ?, ?, ?, ?)");
      preparedStatement.setString(1, name);
      preparedStatement.setString(2, email);
      preparedStatement.setString(3, username);
      preparedStatement.setString(4, password);
      preparedStatement.setString(5, birthday);
      preparedStatement.setString(6, sex);
      preparedStatement.executeUpdate();

    } catch (Exception e) {
      throw e;
    } finally {
      close();
    }

  }
  
  public int getNoOfRows(String username) throws Exception {
  int rows = 0;
  try{
      
      Class.forName("com.mysql.jdbc.Driver");
      
      connect = DriverManager
          .getConnection("jdbc:mysql://localhost/lwif?"
              + "user=root&password=root");
      
      String sql = "SELECT count(*) as count FROM users where username=?";
      preparedStatement = connect
          .prepareStatement(sql);
      preparedStatement.setString(1, username);
      ResultSet rs = preparedStatement.executeQuery();

      while(rs.next()){
         rows  = rs.getInt("count");
      }

      rs.close();
      return rows;
   }catch(Exception e){
      //Handle errors for Class.forName
      e.printStackTrace();
   }finally{
      //finally block used to close resources
      try{
         if(preparedStatement!=null)
            connect.close();
      }catch(SQLException se){
      }// do nothing
      try{
         if(connect!=null)
            connect.close();
      }catch(SQLException se){
         se.printStackTrace();
      }
   }
   return rows;
  
  }
  
  /*Check for password username authentication*/
  public int getNoOfRowsForPasswordAuthentication(String username, String password) throws Exception {
  int rows = 0;
  try{
      
      Class.forName("com.mysql.jdbc.Driver");
      
      connect = DriverManager
          .getConnection("jdbc:mysql://localhost/lwif?"
              + "user=root&password=root");
      
      String sql = "SELECT count(*) as count FROM users where username=? and password=?";
      preparedStatement = connect
          .prepareStatement(sql);
      preparedStatement.setString(1, username);
      preparedStatement.setString(2, password);
      ResultSet rs = preparedStatement.executeQuery();

      while(rs.next()){
         rows  = rs.getInt("count");
      }

      rs.close();
      return rows;
   }catch(Exception e){
      //Handle errors for Class.forName
      e.printStackTrace();
   }finally{
      //finally block used to close resources
      try{
         if(preparedStatement!=null)
            connect.close();
      }catch(SQLException se){
      }// do nothing
      try{
         if(connect!=null)
            connect.close();
      }catch(SQLException se){
         se.printStackTrace();
      }
   }
   return rows;
  
  }

  // you need to close all three to make sure
  private void close() {
  }
} 
